/**
 * 
 */
package com.hospital.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.hospital.dao.IExpertDao;
import com.hospital.vo.Expert;

/**
 * @author Administrator
 *
 */
public class ExpertDaoImpl implements IExpertDao {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	public ExpertDaoImpl(Connection conn) {
		this.conn = conn;
	}
	/* (non-Javadoc)
	 * @see com.hospital.dao.IExpertDao#doCreate(com.hospital.vo.Expert)
	 */
	@Override
	public boolean doCreate(Expert expert) throws Exception {
		boolean flag = false;
		try {
			String sql = "INSERT INTO expert(kid,ename,content,pubdate,imgdir) VALUES(?,?,?,?,?)";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, expert.getKid());
			this.pstmt.setString(2, expert.getEname());
			this.pstmt.setString(3, expert.getContent());
			this.pstmt.setTimestamp(4, new java.sql.Timestamp(expert.getPubdate().getTime()));
			this.pstmt.setString(5, expert.getImgdir());
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	/* (non-Javadoc)
	 * @see com.hospital.dao.IExpertDao#doUpdate(com.hospital.vo.Expert)
	 */
	@Override
	public boolean doUpdate(Expert expert) throws Exception {
		boolean flag = false;
		try {
			String sql = "UPDATE expert SET kid=?,ename=?,content=?,imgdir=? WHERE eid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, expert.getKid());
			this.pstmt.setString(2, expert.getEname());
			this.pstmt.setString(3, expert.getContent());
			this.pstmt.setString(4, expert.getImgdir());
			this.pstmt.setInt(5, expert.getEid());
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	/* (non-Javadoc)
	 * @see com.hospital.dao.IExpertDao#doDel(int)
	 */
	@Override
	public boolean doDel(int eid) throws Exception {
		boolean flag = false;
		try {
			String sql = "DELETE FROM expert WHERE eid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, eid);
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	/* (non-Javadoc)
	 * @see com.hospital.dao.IExpertDao#findAll(java.lang.String, int, int)
	 */
	@Override
	public List<Expert> findAll(String keyWord, int startIndex, int count)
			throws Exception {
		List<Expert> list = null;
		keyWord = (keyWord == null) ? "" : keyWord;
		try {
			String sql = "SELECT expert.*,keshi.* " +
					"FROM expert " +
					"LEFT JOIN " +
					"keshi ON expert.kid=keshi.kid " +
					"WHERE expert.ename LIKE ? OR expert.content LIKE ? " +
					"LIMIT ?,?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, "%" + keyWord + "%");
			this.pstmt.setString(2, "%" + keyWord + "%");
			this.pstmt.setInt(3, startIndex);
			this.pstmt.setInt(4, count);
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<Expert>();
			while(this.rs.next()) {
				Expert exp = new Expert();
				exp.setEid(this.rs.getInt("expert.eid"));
				exp.setEname(this.rs.getString("expert.ename"));
				exp.setKname(this.rs.getString("keshi.kname"));
				exp.setContent(this.rs.getString("expert.content"));
				exp.setPubdate(this.rs.getDate("expert.pubdate"));
				exp.setImgdir(this.rs.getString("expert.imgdir"));
				list.add(exp);
				
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}

	/* (non-Javadoc)
	 * @see com.hospital.dao.IExpertDao#findById(int)
	 */
	@Override
	public Expert findById(int eid) throws Exception {
		Expert exp = null;
		try {
			String sql = "SELECT expert.*,keshi.* " +
					"FROM expert " +
					"LEFT JOIN " +
					"keshi ON expert.kid=keshi.kid " +
					"WHERE eid = ?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, eid);
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				exp = new Expert();
				exp.setEid(this.rs.getInt("expert.eid"));
				exp.setKid(this.rs.getInt("expert.kid"));
				exp.setEname(this.rs.getString("expert.ename"));
				exp.setContent(this.rs.getString("expert.content"));
				exp.setPubdate(this.rs.getDate("expert.pubdate"));
				exp.setImgdir(this.rs.getString("expert.imgdir"));
				exp.setKname(this.rs.getString("keshi.kname"));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return exp;
	}
	@Override
	public int count(String keyWord) throws Exception {
		int count = 0;
		keyWord = (keyWord == null) ? "" : keyWord;
		try {
			String sql = "SELECT COUNT(*) AS count " +
					"FROM expert " +
					"WHERE ename LIKE ? OR content LIKE ? " ;
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, "%" + keyWord + "%");
			this.pstmt.setString(2, "%" + keyWord + "%");
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				count = this.rs.getInt("count");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return count;
	}
	@Override
	public List<Expert> findAllByKid(int kid, int startIndex, int count)
			throws Exception {
		List<Expert> list = null;
		try {
			String sql = "SELECT * " +
					"FROM expert " +
					"WHERE kid=? " +
					"LIMIT ?,?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, kid);
			this.pstmt.setInt(2, startIndex);
			this.pstmt.setInt(3, count);
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<Expert>();
			while(this.rs.next()) {
				Expert exp = new Expert();
				exp.setEid(this.rs.getInt("eid"));
				exp.setEname(this.rs.getString("ename"));
				exp.setKid(this.rs.getInt("kid"));
				exp.setContent(this.rs.getString("content"));
				exp.setPubdate(this.rs.getDate("pubdate"));
				exp.setImgdir(this.rs.getString("imgdir"));
				list.add(exp);
				
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}
	@Override
	public int count(int kid) throws Exception {
		int count = 0;
		try {
			String sql = "SELECT COUNT(*) AS count " +
					"FROM expert " +
					"WHERE kid=? " ;
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, kid);
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				count = this.rs.getInt("count");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return count;
	}

}
